package dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class Student{
    private static Connection connection=DBConnection.getDBCourseConnection();
    public static String getPwdById(String sid) {
        String sql="select pwd from student where sid=?";
        PreparedStatement preparedStatement= null;
        String pwd=null;
        try {
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1,sid);
            ResultSet resultSet=preparedStatement.executeQuery();
            if(resultSet.next()){
                pwd=resultSet.getString("pwd");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return pwd;
    }

    public static String getNameById(String sid){
        String sql="select sname from student where sid=?";
        PreparedStatement preparedStatement= null;
        String name=null;
        try {
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1,sid);
            ResultSet resultSet=preparedStatement.executeQuery();
            if(resultSet.next()){
                name=resultSet.getString("sname");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return name;
    }

    public static String getSchoolById(String sid){
        String sql="select school from student where sid=?";
        PreparedStatement preparedStatement= null;
        String result=null;
        try {
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1,sid);
            ResultSet resultSet=preparedStatement.executeQuery();
            if(resultSet.next()){
                result=resultSet.getString("school");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return result;
    }

    public static String getMajorById(String sid){
        String sql="select major from student where sid=?";
        PreparedStatement preparedStatement= null;
        String result=null;
        try {
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1,sid);
            ResultSet resultSet=preparedStatement.executeQuery();
            if(resultSet.next()){
                result=resultSet.getString("major");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return result;
    }

    public static String getGradeById(String sid){
        String sql="select grade from student where sid=?";
        PreparedStatement preparedStatement= null;
        String result=null;
        try {
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1,sid);
            ResultSet resultSet=preparedStatement.executeQuery();
            if(resultSet.next()){
                result=resultSet.getString("grade");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return result;
    }

    public static String getDegreeById(String sid){
        String sql="select degree from student where sid=?";
        PreparedStatement preparedStatement= null;
        String result=null;
        try {
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1,sid);
            ResultSet resultSet=preparedStatement.executeQuery();
            if(resultSet.next()){
                result=resultSet.getString("degree");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return result;
    }

    public static String getGenderById(String sid){
        String sql="select gender from student where sid=?";
        PreparedStatement preparedStatement= null;
        String result=null;
        try {
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1,sid);
            ResultSet resultSet=preparedStatement.executeQuery();
            if(resultSet.next()){
                result=resultSet.getString("gender");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return result;
    }

    public static String getBirthdayById(String sid){
        String sql="select birthday from student where sid=?";
        PreparedStatement preparedStatement= null;
        String result=null;
        try {
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1,sid);
            ResultSet resultSet=preparedStatement.executeQuery();
            if(resultSet.next()){
                result=resultSet.getString("birthday");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return result;
    }

    public static String getHeadPathById(String sid){
        String sql="select headPath from student where sid=?";
        PreparedStatement preparedStatement= null;
        String result=null;
        try {
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1,sid);
            ResultSet resultSet=preparedStatement.executeQuery();
            if(resultSet.next()){
                result=resultSet.getString(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return result;
    }

    public static boolean hasSid(String sid){
        String sql="select sid from student where sid=?";
        PreparedStatement preparedStatement= null;
        boolean result=false;
        try {
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1,sid);
            ResultSet resultSet=preparedStatement.executeQuery();
            if(resultSet.next()){
                result=true;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return result;
    }

    public static boolean insert(String sid, String sname, String pwd){
        String sql="insert into student(sid,sname,pwd) values(?,?,?)";
        PreparedStatement preparedStatement= null;
        boolean result=false;
        try {
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1,sid);
            preparedStatement.setString(2,sname);
            preparedStatement.setString(3,pwd);
            int i=preparedStatement.executeUpdate();
            if(i==1){
                result=true;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return result;
    }

    public static boolean updateInfoById(String school,String major,String grade,String degree,String gender,String birthday,String id){
        String sql="update student set school=?,major=?,grade=?,degree=?,gender=?,birthday=? where sid=?";
        PreparedStatement preparedStatement=null;
        boolean result=false;
        try {
            preparedStatement=connection.prepareStatement(sql);
            preparedStatement.setString(1,school);
            preparedStatement.setString(2,major);
            preparedStatement.setString(3,grade);
            preparedStatement.setString(4,degree);
            preparedStatement.setString(5,gender);
            preparedStatement.setString(6,birthday);
            preparedStatement.setString(7,id);
            int i=preparedStatement.executeUpdate();
            if(i==1){
                result=true;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return result;
    }

    public static boolean updateNameById(String sname,String id){
        String sql="update student set sname=? where sid=?";
        PreparedStatement preparedStatement=null;
        boolean result=false;
        try {
            preparedStatement=connection.prepareStatement(sql);
            preparedStatement.setString(1,sname);
            preparedStatement.setString(2,id);
            int i=preparedStatement.executeUpdate();
            if(i==1){
                result=true;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return result;
    }

    public static boolean updateAllById(String nickname, String school, String major, String grade, String degree, String gender, String birthday, String headPath, String id) {
        String sql="update student set sname=?,school=?,major=?,grade=?,degree=?,gender=?,birthday=?,headPath=? where sid=?";
        PreparedStatement preparedStatement=null;
        boolean result=false;
        try {
            preparedStatement=connection.prepareStatement(sql);
            preparedStatement.setString(1,nickname);
            preparedStatement.setString(2,school);
            preparedStatement.setString(3,major);
            preparedStatement.setString(4,grade);
            preparedStatement.setString(5,degree);
            preparedStatement.setString(6,gender);
            preparedStatement.setString(7,birthday);
            preparedStatement.setString(8,headPath);
            preparedStatement.setString(9,id);
            int i=preparedStatement.executeUpdate();
            if(i==1){
                result=true;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return result;
    }
}
